# -*- coding: utf-8 -*-
import json
import os
from common import log
import pandas as pd
from sqlalchemy import create_engine


class MySql:
    """用于执行数据库操作的类"""

    def __init__(self, engine):
        """

        :param engine: 数据库连接信息
        """
        self.logger = log.logger

        # 初始化配置文件
        path_dir = str(
            os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir, 'sql/db.json')))
        if not os.path.exists(path_dir):
            raise FileNotFoundError("请确保测试数据文件存在！")
        with open(path_dir) as fb:
            data = json.load(fb)

        self.engine = engine
        self.engine_type = data[engine]['engine_type']
        self.host = data[engine]['host']
        self.port = data[engine]['port']
        self.database = data[engine]['database']
        self.username = data[engine]['username']
        self.password = data[engine]['password']

    def mysql_connect(self):
        """连接数据库"""
        if self.engine_type == 'mysql':
            connection = create_engine(
                f'mysql+pymysql://{self.username}:{self.password}@{self.host}:{self.port}/{self.database}')
            return connection
        if self.engine_type == 'oracle':
            connection = create_engine(
                f'oracle://{self.username}:{self.password}@{self.host}:{self.port}/{self.database}')
            return connection

    def execute(self, sql):
        """
        :param sql: 待执行语句
        :return:
        """
        # 打开数据库连接
        conn = self.mysql_connect()
        if sql.find("select") == 0:
            try:
                df_read = pd.read_sql_query(sql, conn)
                return df_read
            except Exception as e:
                self.logger.info(f'{sql}sql执行失败，已回滚' + str(e), extra={'appname': ''})
            # 关闭数据库连接
            conn.close()
        else:
            try:
                # conn = self.mysql_connect()
                conn.execute(sql)
            except Exception as e:
                self.logger.info(f'{sql}sql执行失败，已回滚' + str(e), extra={'appname': ''})


if __name__ == '__main__':
    pass